package com.techysky.repository;

import java.io.Serializable;
import java.util.List;

import org.springframework.data.jpa.repository.Query;

import com.techysky.entity.PlanManage;

public interface PlanManageRepository extends CoreRepository<PlanManage, Serializable> {
	
	@Query(value="select  t.* from  plan_manage  t  where   t.user_id=?1  order by t.create_date desc ", nativeQuery = true)
    List<PlanManage> findAllPlans(String userId);

	List<PlanManage> findAllByPalnTypeIdOrderByCreateDateDesc(String planTypeId);
	
	
	List<PlanManage> findAllByPalnTypeIdAndUserIdOrderByCreateDateDesc(String planTypeId,String userId);
	
	List<PlanManage> findAllByUserIdOrderByCreateDateDesc(String userId);
	
	@Query(value="select COUNT(*) from  plan_manage  t  where  t.task_state=?1", nativeQuery = true)
	int findYwc(String string);
	
	@Query(value="select COUNT(*) from  plan_manage  t  where  t.task_state=?1 and t.plan_node<?2 ", nativeQuery = true)
	int findYc(String string, String getSystemTime);
	
	@Query(value="select COUNT(*) from  plan_manage  t  where  t.task_state=?1 and t.plan_node>=?2 and t.plan_star>?2 ", nativeQuery = true)
	int findWks(String string, String getSystemTime);
	@Query(value="select COUNT(*) from  plan_manage  t  where  t.task_state=?1 and t.plan_node>=?2 and t.plan_star<=?2 ", nativeQuery = true)
	int findYks(String string, String getSystemTime);
	
	@Query(value="select COUNT(*) from  plan_manage  t  where  t.task_state=?1 and t.user_id=?2", nativeQuery = true)
	int findYwc0(String string, String userId);
	
	@Query(value="select COUNT(*) from  plan_manage  t  where  t.task_state=?1 and t.plan_node<?2 and t.user_id=?3 ", nativeQuery = true)
	int findYc0(String string, String getSystemTime, String userId);
	
	@Query(value="select COUNT(*) from  plan_manage  t  where  t.task_state=?1 and t.plan_node>=?2 and t.user_id=?3  and t.plan_star>?2", nativeQuery = true)
	int findWks0(String string, String getSystemTime, String userId);
	@Query(value="select COUNT(*) from  plan_manage  t  where  t.task_state=?1 and t.plan_node>=?2 and t.user_id=?3  and t.plan_star<=?2 ", nativeQuery = true)
	int findYks0(String string, String getSystemTime, String userId);
	
	@Query(value="select distinct(Left(t.plan_Star,4)) from  plan_manage  t  GROUP BY Left(t.plan_Star,4)  order by Left(t.plan_Star,4) asc ", nativeQuery = true)
	List<Object> findYears();
	@Query(value="select distinct(Left(t.plan_Star,4)) from  plan_manage  t where  t.user_id=?1  GROUP BY Left(t.plan_Star,4)  order by Left(t.plan_Star,4) asc ", nativeQuery = true)
	List<Object> findYears1(String userId);
	
	@Query(value="select COUNT(*) from  plan_manage  t  where  t.task_state=?1 and Left(t.plan_Star,4)=?2", nativeQuery = true)
	int findYwc1(String string, Object object);

	@Query(value="select COUNT(*) from  plan_manage  t  where  t.task_state=?1 and t.plan_node>=?2 and Left(t.plan_Star,4)=?3", nativeQuery = true)
	int findWwc1(String string, String getSystemTime, Object object);

	@Query(value="select COUNT(*) from  plan_manage  t  where  t.task_state=?1 and t.plan_node<?2 and Left(t.plan_Star,4)=?3", nativeQuery = true)
	int findYc1(String string, String getSystemTime, Object object);

	@Query(value="select COUNT(*) from  plan_manage  t  where  t.task_state=?1 and Left(t.plan_Star,4)=?2 and t.user_id=?3", nativeQuery = true)
	int findYwc2(String string, Object object, String userId);

	@Query(value="select COUNT(*) from  plan_manage  t  where  t.task_state=?1 and t.plan_node>=?2 and Left(t.plan_Star,4)=?3 and t.user_id=?4", nativeQuery = true)
	int findWwc2(String string, String getSystemTime, Object object, String userId);

	@Query(value="select COUNT(*) from  plan_manage  t  where  t.task_state=?1 and t.plan_node<?2 and Left(t.plan_Star,4)=?3 and t.user_id=?4", nativeQuery = true)
	int findYc2(String string, String getSystemTime, Object object, String userId);
	
}